﻿using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;

namespace WebserviceSafeData
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        private SqlConnection setConnection;

        [WebMethod]
        public string HelloWorld()
        {
            var connString = ConfigurationManager.ConnectionStrings["LocalSqlServer"];
            string connDatabaseString = connString.ConnectionString;
            SqlConnection conn = new SqlConnection(connDatabaseString);
            try
            {
                conn.Open();
                return "Connected!";
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e);
                return "Disconected!";
            }
        }

        [WebMethod]
        public bool CheckUsername(string username)
        {
            setConnection = Connector.Connect();
            string sqlQuery = "select * from ACCOUNTS where USER_NAME = '" + username + "'";
            SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
            SqlDataReader read = cmd.ExecuteReader();
            bool checkState = !read.Read();
            read.Close();
            return checkState;
        }

        [WebMethod]
        public bool CheckEmail(string email)
        {
            string sqlQuery = "select * from ACCOUNTS where EMAIL = '" + email + "'";
            SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
            SqlDataReader read = cmd.ExecuteReader();
            bool checkState = !read.Read();
            read.Close();
            return checkState;
        }

        [WebMethod]
        public bool CheckPassword(string password)
        {
            string sqlQuery = "select * from ACCOUNTS where PASSWORD = '" + password + "'";
            SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
            SqlDataReader read = cmd.ExecuteReader();
            bool checkState = !read.Read();
            read.Close();
            return checkState;
        }

        [WebMethod]
        public string Login(string username, string password)
        {
            string sql = "select * from ACCOUNTS where USER_NAME = '" + username + "' and PASSWORD = '" + password + "'";
            SqlCommand cmd = new SqlCommand(sql, Connector.Connect());
            SqlDataReader reader = cmd.ExecuteReader();
            return reader.Read() ? reader["USER_ID"].ToString().Trim() : "-1";
        }

        [WebMethod]
        public string RevertPassword(string email)
        {
            if (!CheckEmail(email))
            {
                string sqlQuery = "select PASSWORD from ACCOUNTS where EMAIL = '" + email + "'";
                SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    for (int i = 0; i < read.FieldCount; i++)
                        return read.GetValue(i).ToString();
                }
            }
            return null;
        }

        [WebMethod]
        public string CreateNewAccount(string username, string password, string email)
        {
            if (!CheckUsername(username))
            {
                return "Username is existed!";
            }
            else if (!CheckEmail(email))
            {
                return "Email is existed!";
            }
            else
            {
                string sqlQuery = "insert into ACCOUNTS(USER_NAME, PASSWORD, EMAIL) values('" + username + "','" + password + "','" + email + "')";
                SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
                try
                {
                    return (cmd.ExecuteNonQuery() > 0) ? "Your Account is created!" : "Error while create Account!";
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return e + "Error query to Database";
                }
            }
        }

        [WebMethod]
        public String GetAccountFromEmail(String email)
        {
            string sqlQuery = "select * from ACCOUNTS where EMAIL = '" + email + "'";
            SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                String account = reader["USER_NAME"] + "/" + reader["PASSWORD"];
                Connector.Connect().Close();
                return account;//Neu co ton tai email thif lay user tuong ung
            }
            Connector.Connect().Close();
            return " ";//Neu khong ton tai email thi tra ve chinh chuoi da truyen vao
        }

        [WebMethod]
        public String GetEmailPasswordFromUsername(String username)
        {
            string sqlQuery = "select * from ACCOUNTS where USER_NAME = '" + username + "'";
            SqlCommand cmd = new SqlCommand(sqlQuery, Connector.Connect());
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                String email_password = reader["email"].ToString() + "/" + reader["password"].ToString();
                Connector.Connect().Close();
                return email_password;//Neu co ton tai email thi lay user tuong ung
            }
            Connector.Connect().Close();
            return " ";//Neu khong ton tai email thi tra ve chinh chuoi da truyen vao
        }

        [WebMethod]
        public String sendEmail(String from, String to)
        {
            return "any messages when send successed or otherwise";//TODO
        }

        [WebMethod]
        public bool ChangePassword(String usernameCurrent, String newPassword)
        {
            string sql = "update dbo.ACCOUNTS set PASSWORD = '" + newPassword + "' where USER_NAME = '" + usernameCurrent + "'";
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            return command.ExecuteNonQuery() > 0;
        }

        [WebMethod]
        public bool BackupContact(String xml, String user_id, String date, String time, String note)
        {
            String sql = "insert into dbo.BACKUP_CONTACT(CONTENT_BACKUP, USER_ID, DATE, TIME, NOTE) values('" + xml + "','" + user_id + "','" + date + "', '" + time + "','" + note + "')";
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            bool r = command.ExecuteNonQuery() > 0;
            Connector.Connect().Close();
            return r;
        }

        [WebMethod]
        public String RestoreContact(int version_id)
        {
            String sql = "select CONTENT_BACKUP from BACKUP_CONTACT where VERSION_ID = " + version_id;
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                String content = reader["CONTENT_BACKUP"].ToString();
                reader.Close();
                Connector.Connect().Close();
                return content;
            }
            Connector.Connect().Close();
            return " ";
        }

        [WebMethod]
        public List<BackupInfo> GetAllBackupInfo(String user_id)
        {
            string sql = "select * from BACKUP_CONTACT where USER_ID = " + user_id;
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            SqlDataReader reader = command.ExecuteReader();
            List<BackupInfo> al = new List<BackupInfo>();
            while (reader.Read())
            {
                string date = reader["DATE"].ToString();
                string time = reader["TIME"].ToString();
                string notes = reader["NOTE"].ToString() + " ";//get anytype{}
                string id = reader["VERSION_ID"].ToString();
                al.Add(new BackupInfo(id, date, time, notes));
            }
            al.Reverse();
            return al;
        }

        [WebMethod]
        public bool DeleteBackupVersion(String id)
        {
            string sql = "delete from BACKUP_CONTACT where VERSION_ID = " + id;
            SqlCommand cmd = new SqlCommand(sql, Connector.Connect());
            bool r = cmd.ExecuteNonQuery() > 0;
            Connector.Connect().Close();
            return r;
        }

        [WebMethod]
        public String UploadFolderPath()
        {
            String path = Server.MapPath(".");
       
            String newPath = System.IO.Path.Combine(path, "myFolder");
            System.IO.Directory.CreateDirectory(newPath);

            return newPath;
        }

        [WebMethod]
        public bool BackupSMS(String xml, String user_id, String sms_date, String sms_time, String sms_note)
        {
            String sql = "insert into dbo.SMS_BACKUP(SMS_CONTENT, USER_ID, SMS_DATE, SMS_TIME, SMS_NOTE) values('" + xml + "','"+ user_id + "','" + sms_date + "','" + sms_time + "','" + sms_note + "')";
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            bool r = command.ExecuteNonQuery() > 0;
            Connector.Connect().Close();
            return r;
        }

        [WebMethod]
        public String RestoreSMS(int sms_backup_id)
        {
            String sql = "select SMS_CONTENT from SMS_BACKUP where sms_backup_id = " + sms_backup_id;
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                String content = reader["SMS_CONTENT"].ToString();
                reader.Close();
                Connector.Connect().Close();
                return content;
            }
            Connector.Connect().Close();
            return " ";
        }

        [WebMethod]
        public List<BackupInfo> GetAllSMSBackupInfo(String user_id)
        {
            string sql = "select * from SMS_BACKUP where USER_ID = " + user_id;
            SqlCommand command = new SqlCommand(sql, Connector.Connect());
            SqlDataReader reader = command.ExecuteReader();
            List<BackupInfo> al = new List<BackupInfo>();
            while (reader.Read())
            {
                string date = reader["SMS_DATE"].ToString();
                string time = reader["SMS_TIME"].ToString();
                string notes = reader["SMS_NOTE"].ToString() + " ";//get anytype{}
                string id = reader["SMS_BACKUP_ID"].ToString();
                al.Add(new BackupInfo(id, date, time, notes));
            }
            al.Reverse();
            return al;
        }

        [WebMethod]
        public bool DeleteSMSBackupVersion(String sms_backup_id)
        {
            string sql = "delete from SMS_BACKUP where SMS_BACKUP_ID = " + sms_backup_id;
            SqlCommand cmd = new SqlCommand(sql, Connector.Connect());
            bool r = cmd.ExecuteNonQuery() > 0;
            Connector.Connect().Close();
            return r;
        }


    }
}
